﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Workbench.Models;

namespace Workbench.Persistance
{
    public class ExerciseRepository : RepositoryBase, IDisposable
    {
        public ExerciseRepository(Func<SQLiteConnection> connectionProvider)
            : base(connectionProvider)
        {
            connection = connectionProvider();
        }

        public void PersistExerciseSuite(ExerciseSuiteModel model)
        {
            using (var cmd = connection.CreateCommand())
            {
                if (transaction != null) cmd.Transaction = transaction;
                cmd.CommandText = @"INSERT INTO ExerciseSuites (Name) VALUES (@Name); SELECT last_insert_rowid();";
                cmd.Parameters.AddWithValue("@Name", model.Name);
                model.RowId = DbUtility.ExecuteScalar<long>(cmd);
            }
        }

        public void RelateSuiteAndExercise(long exerciseSuiteId, long exerciseId)
        {
            using (var cmd = connection.CreateCommand())
            {
                if (transaction != null) cmd.Transaction = transaction;
                cmd.CommandText = @"INSERT INTO ExerciseSuites_Exercises (ExerciseSuiteId, ExerciseId, DisplayOrder) VALUES (@suiteId, @exerciseId, (SELECT IFNULL(MAX(DisplayOrder) + 1,1) AS NextDisplayOrder FROM ExerciseSuites_Exercises WHERE ExerciseSuiteId = @suiteId));";
                cmd.Parameters.AddWithValue("@suiteId", exerciseSuiteId);
                cmd.Parameters.AddWithValue("@exerciseId", exerciseId);
                cmd.ExecuteNonQuery();
            }
        }
        public ExerciseSuiteModel GetTestSuite(string name)
        {
            using (var cmd = connection.CreateCommand())
            {
                if (transaction != null) cmd.Transaction = transaction;
                cmd.CommandText = @"SELECT RowId, Name FROM ExerciseSuites WHERE Name = @Name";
                cmd.Parameters.AddWithValue("@Name", name);

                using (var rdr = cmd.ExecuteReader())
                {
                    ExerciseSuiteModel result = null;
                    if (rdr.Read())
                    {
                        result = new ExerciseSuiteModel();
                        result.RowId = rdr.GetInt64(0);
                        result.Name = rdr.GetString(1);
                    }
                    return result;
                }
            }
        }

        public List<ExerciseSuiteModel> GetAllExerciseSuites()
        {
            using (var cmd = connection.CreateCommand())
            {
                if (transaction != null) cmd.Transaction = transaction;
                cmd.CommandText = @"SELECT RowId, Name FROM ExerciseSuites ORDER BY Name";

                using (var rdr = cmd.ExecuteReader())
                {
                    var result = new List<ExerciseSuiteModel>();
                    while (rdr.Read())
                    {
                        var model = new ExerciseSuiteModel();
                        model.RowId = rdr.GetInt64(0);
                        model.Name = rdr.GetString(1);
                        result.Add(model);
                    }
                    return result;
                }
            }
        }

        public List<ConcordancerExerciseModel> GetExercises(long exerciseSuiteId)
        {
            using (var cmd = connection.CreateCommand())
            {
                if (transaction != null) cmd.Transaction = transaction;
                cmd.CommandText =
@"SELECT e.RowId, e.Name, e.UniqueWordId, uw.Content, e.BaseFormId, bf.Content, e.POSTypeId
FROM ExerciseSuites_Exercises ese
INNER JOIN Exercises e ON e.RowId = ese.ExerciseId
LEFT JOIN UniqueWords uw ON uw.RowId = e.UniqueWordId
LEFT JOIN BaseForms bf ON bf.RowId = e.BaseFormId
WHERE ExerciseSuiteId = @Id";
                cmd.Parameters.AddWithValue("@Id", exerciseSuiteId);

                var result = new List<ConcordancerExerciseModel>();
                using (var rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        var model = new ConcordancerExerciseModel();
                        model.RowId = rdr.GetInt64(0);
                        model.Name = rdr.GetString(1);
                        if (!rdr.IsDBNull(2))
                        {
                            model.Word = new WordSuggestionModel()
                            {
                                RowId = rdr.GetInt64(2),
                                Content = rdr.GetString(3),
                                SuggestionType = SuggestionType.WordForm
                            };
                        }
                        else if (!rdr.IsDBNull(4))
                        {
                            model.Word = new WordSuggestionModel()
                            {
                                RowId = rdr.GetInt64(4),
                                Content = rdr.GetString(5),
                                SuggestionType = SuggestionType.BaseForm
                            };
                        }
                        result.Add(model);
                    }
                }

                using(var cmdSentences = connection.CreateCommand())
                {
                    if (transaction != null) cmd.Transaction = transaction;
                    cmdSentences.CommandText = @"SELECT SentenceId FROM Exercises_Sentences WHERE ExerciseId = @exerciseId";
                    
                    var paramExerciseId = new SQLiteParameter("@exerciseId");
                    cmdSentences.Parameters.Add(paramExerciseId);

                    foreach(var exercise in result)
                    {
                        paramExerciseId.Value = exercise.RowId;
                        using(var rdr = cmdSentences.ExecuteReader())
                        {
                            while (rdr.Read())
                            {
                                exercise.SentenceIds.Add(rdr.GetInt64(0));
                            }
                        }
                    }
                }

                return result;
            }
        }

        public void PersistConcordancerExercise(ConcordancerExerciseModel model)
        {
            using (var cmd = connection.CreateCommand())
            {
                if (transaction != null) cmd.Transaction = transaction;

                string column = "Invalid";
                switch (model.Word.SuggestionType)
                {
                    case SuggestionType.BaseForm: column = @"BaseFormId"; break;
                    case SuggestionType.WordForm: column = @"UniqueWordiD"; break;
                }

                cmd.CommandText = string.Format(@"INSERT INTO Exercises (Name, {0}) VALUES (@Name, @Id);  SELECT last_insert_rowid();", column);
                cmd.Parameters.AddWithValue("@Name", model.Name);
                cmd.Parameters.AddWithValue("@Id", model.Word.RowId);
                model.RowId = DbUtility.ExecuteScalar<long>(cmd);

                cmd.CommandText = @"INSERT INTO Exercises_Sentences (ExerciseId, SentenceId, DisplayOrder) VALUES (@exerciseId, @sentenceId, (SELECT IFNULL(MAX(DisplayOrder) + 1,1) AS NextDisplayOrder FROM Exercises_Sentences WHERE ExerciseId = @exerciseId));";
                cmd.Parameters.Clear();
                var paramExercise = new SQLiteParameter("@exerciseId");
                cmd.Parameters.Add(paramExercise);
                var paramSentence = new SQLiteParameter("@sentenceId");
                cmd.Parameters.Add(paramSentence);

                foreach (var sentenceId in model.SentenceIds)
                {
                    paramExercise.Value = model.RowId;
                    paramSentence.Value = sentenceId;
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public void DeleteExercise(ConcordancerExerciseModel exerciseModel)
        {
            var cmd = new SQLiteCommand(@"DELETE FROM Exercises WHERE RowId = @rowId", connection);
            cmd.Parameters.AddWithValue("@rowId", exerciseModel.RowId);
            cmd.ExecuteNonQuery();
        }

        public void DeleteExerciseSuite(ExerciseSuiteModel suiteModel)
        {
            var cmd = new SQLiteCommand(@"DELETE FROM ExerciseSuites WHERE RowId = @rowId", connection);
            cmd.Parameters.AddWithValue("@rowId", suiteModel.RowId);
            cmd.ExecuteNonQuery();
        }


        protected override void Dispose(bool disposing)
        {
            base.Dispose(disposing);
            if (disposing)
            {
                if (connection != null)
                {
                    connection.Dispose();
                    connection = null;
                }
            }
        }
    }
}
